/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.Allowances;
import java.sql.Connection;
import utils.SQLServerDB;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Hai Muoi Thang Sau
 */
public class AllowanceDAO {

    final String SQL_CREATE = "INSERT INTO Allowances VALUES(?,?,?)";
    final String SQL_READALL = "SELECT * FROM Allowances";
    final String SQL_READBYPOSTID = "SELECT * FROM Allowances WHERE ID=?";
    final String SQL_READBYLOCATIONTYPEID = "SELECT * FROM Allowances WHERE SupplierID=?";
    final String SQL_UPDATE = "UPDATE Allowances SET LocationTypeId=?, salaryCoefficient WHERE POSTID=?";
    final String SQL_DELETE = "DELETE FROM Allowances WHERE ID=?";
    private Connection conn;
    private String lastError;

    public AllowanceDAO() {

    }

    public boolean create(Allowances a) {
        conn = SQLServerDB.getConn();
        try {
            PreparedStatement pr = conn.prepareStatement(SQL_CREATE);
            pr.setInt(1, a.getPostId());
            pr.setInt(2, a.getLocationTypeId());
            pr.setString(3, a.getSalaryCoefficient());
            if (pr.executeUpdate() > 0) {
                setLastError("Create Successful!");
                return true;
            } else {
                setLastError("Error: Create Failed!");
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return false;
    }

    public ArrayList<Allowances> readAll() {
        conn = SQLServerDB.getConn();
        ArrayList<Allowances> all = new ArrayList<>();

        try {
            PreparedStatement pr = conn.prepareStatement(SQL_READALL);
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                Allowances r = new Allowances();
                r.setPostId(rs.getInt("ID"));
                r.setLocationTypeId(rs.getInt("LocationTypeId"));
                r.setSalaryCoefficient(rs.getString("SalaryCoefficient"));
                all.add(r);
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return all;
    }

    public Allowances readByPostId(int id) {
        conn = SQLServerDB.getConn();
        Allowances all = new Allowances();
        try {
            PreparedStatement pr = conn.prepareStatement(SQL_READBYPOSTID);
            pr.setInt(1, id);
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                all.setPostId(rs.getInt("ID"));
                all.setLocationTypeId(rs.getInt("LocationTypeId"));
                all.setSalaryCoefficient(rs.getString("SalaryCoefficient"));
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return all;
    }

    public boolean update(Allowances a) {
        conn = SQLServerDB.getConn();
        try {
            PreparedStatement pr = conn.prepareStatement(SQL_UPDATE);
            pr.setInt(1, a.getPostId());
            pr.setInt(2, a.getLocationTypeId());
            pr.setString(3, a.getSalaryCoefficient());
            if (pr.executeUpdate() > 0) {
                setLastError("Update Successful!");
                return true;
            } else {
                setLastError("Error: Update Failed!");
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return false;
    }

    public boolean delete(int id) {
        conn = SQLServerDB.getConn();
        try {

            PreparedStatement pr = conn.prepareStatement(SQL_DELETE);
            pr.setInt(1, id);
            int r = pr.executeUpdate();
            if (pr.executeUpdate() > 0) {
                setLastError("Delete Successful!");
                return true;
            } else {
                setLastError("Error: Delete Failed!");
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return false;
    }

    public static void main(String[] args) {
        AllowanceDAO dao = new AllowanceDAO();
//        ArrayList<Allowances> allowances = dao.readAll();
        Allowances a = new Allowances();
        a.setPostId(1);
        a.setLocationTypeId(1);
        a.setSalaryCoefficient("2");
        System.out.println(dao.create(a));
        System.out.println(dao.getLastError());

    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }

}
